'''
# Coding:utf-8
# Project: iiop
# Author: rtf
# Time: 2023-03-24 11:01:32
# FileName: dynamic_excel.py
# Software: PyCharm
'''

import openpyxl
import copy
import re
from openpyxl.styles import Alignment, Border, Side
from func.export_excel_key import insert_rows

'''
获取指定sheet表：sheet = wb['Sheet1']
获取所有的sheet表：wb.sheetnames
按行获取工作表使用区域数据：worksheet.rows
按列获取工作表使用区域数据：worksheet.columns
获取工作表中最小行号：worksheet.min_row
获取工作表中最小列号：worksheet.min_column
获取工作表中最大行号：worksheet.max_row
获取工作表中最大列号：worksheet.max_column
获取单元格的行号：cell.row
获取单元格的列号：cell.column 
iter方法获取指定区域：
1.按行获取指定工作表单元格区域：worksheet.iter_rows(min_row=36,min_col=2,max_col=4,max_row=40)
2.按列获取指定工作表单元格区域：worksheet.iter_cols(min_col=2,min_row=2)
可以通过 min_row、min_col、max_col、max_row 这几个参数进行单元格区域的控制
'''


# 根据偏移量计算单元格位置
def cell_offset(cell, offset):
    col = ""
    row = ""
    for x in cell:
        if x.isdigit():
            row = f"{row}{x}"
        else:
            col = f"{col}{x}"
    row = int(row) + offset
    offset_cell = f"{col}{row}"
    return offset_cell


def dynamic_process(path, sheet_key, sheet_value):
    wb = openpyxl.load_workbook(path)  # 返回一个workbook数据类型的值
    for i, value in enumerate(sheet_value):
        if not value:
            continue
        ws = wb.worksheets[i]
        insert_rows_index = insert_rows.get(f"sheet{i}")
        merged_cells_list = None
        insert_rows_num = 0

        if i > 1 and insert_rows_index:
            rows_height = dict()
            cols_width = dict()
            # 获取rows高度
            for row_index in range(1, ws.max_row + 1):
                rows_height[str(row_index)] = ws.row_dimensions[row_index].height

            # 获取cols宽度
            for col_index in range(1, ws.max_column + 1):
                # 将列索引转换为英文字母表示
                column_letter = openpyxl.utils.get_column_letter(col_index)
                column_width = ws.column_dimensions[column_letter].width
                cols_width[column_letter] = column_width

            merged_cells_list = copy.deepcopy(ws.merged_cells)
            insert_rows_num = len(value.get("item_data")) - 1 if value.get("item_data") else 0
            if insert_rows_num > 0:
                for merged_cell in list(ws.merged_cells): # 拆分单元格
                    if merged_cell.min_row > insert_rows_index:
                        # merged_cell.min_row, merged_cell.max_row, merged_cell.min_col, merged_cell.max_col
                        ws.unmerge_cells(range_string=str(merged_cell))
                ws.insert_rows(insert_rows_index, insert_rows_num) # 在第insert_rows_index行之后插入新行
                new_rows = ws[insert_rows_index: insert_rows_index + insert_rows_num] # 获取新插入的行

                # 创建边框
                border_style = Side(border_style='thin', color='000000')
                border = Border(left=border_style, right=border_style, top=border_style, bottom=border_style)

                # 将边框应用于所有单元格
                for new_row in new_rows:
                    for cell in new_row:
                        cell.border = border

            # 创建一个Alignment对象并将其水平和垂直对齐方式设置为居中
            align = Alignment(horizontal='center', vertical='center')

            # 设置rows高度
            for row_index in range(1, ws.max_row + 1):
                if row_index >= insert_rows_index:
                    if insert_rows_index <= row_index <= insert_rows_index + insert_rows_num:
                        ws.row_dimensions[row_index].height = rows_height.get(str(insert_rows_index))
                        # 遍历所有单元格，将Alignment对象分配给每个单元格
                        for row in ws.iter_rows(min_row=row_index, max_row=row_index):
                            for cell in row:
                                cell.alignment = align
                    else:
                        ws.row_dimensions[row_index].height = rows_height.get(str(row_index - insert_rows_num))

            # 设置cols宽度
            for k, v in cols_width.items():
                ws.column_dimensions[k].width = v

            # 遍历所有单元格
            for row in ws.iter_rows():
                for cell in row:
                    # 如果单元格中有公式，输出公式
                    if cell.data_type == 'f':
                        formula = cell.value
                        if formula:
                            adjusted_formula = cell_formula(formula, insert_rows_num, insert_rows_index)
                            # 将新公式设置为单元格公式
                            cell.value = adjusted_formula

        for k, v in value.items():  # 获取数据中的key，value
            if k == "item_data":
                offset = 0
                for index, v_value in enumerate(v):
                    for k1, v1 in v_value.items():
                        if sheet_key[i].get(k1):
                            # print(i, "**************", sheet_key[i].get(k1), offset)
                            offset_cell = cell_offset(sheet_key[i].get(k1), offset)
                            # print(i, "================", offset_cell)
                            ws[offset_cell] = v1
                    # 因模板中有一行，所以偏移量少加一行
                    if index < len(v) - 1:
                        offset += 1
            else:
                if sheet_key[i].get(k):
                    offset_cell = cell_offset(sheet_key[i].get(k), insert_rows_num)
                    ws[offset_cell] = v

        if i > 1 and insert_rows_index:
           for c in list(merged_cells_list):  # 合并单元格
               if c.min_row > insert_rows_index:
                   r1, r2, c1, c2 = c.min_row + insert_rows_num, c.max_row + insert_rows_num, c.min_col, c.max_col
                   if r1 > insert_rows_index:
                    ws.merge_cells(start_row=r1, start_column=c1, end_row=r2, end_column=c2)

    wb.save(path)


# def cell_formula(formula, insert_rows_num, insert_rows_index):
#     # 找到公式中的所有单元格引用
#     refs = re.findall('[A-Z]\d+', formula)
#     # 遍历所有单元格引用，将行数加insert_rows_num
#     for r in range(len(refs)):
#         row = int(re.findall('\d+', refs[r])[0])
#         if row > insert_rows_index:
#             new_row = row + insert_rows_num
#             refs[r] = re.sub('\d+', str(new_row), refs[r])
#
#     # 使用新的单元格引用构建新公式
#     adjusted_formula = ''
#     for r in range(len(refs)):
#         adjusted_formula += refs[r]
#         if r < len(refs) - 1:
#             adjusted_formula += f"*{r + 1}+"
#     return f"={adjusted_formula}"

def cell_formula(formula, insert_rows_num, insert_rows_index):
    def replace_func(match):
        cell_row = int(match.group(2))
        if cell_row > insert_rows_index:
            new_row = cell_row + insert_rows_num
            return match.group(1) + str(new_row) + match.group(3)
        return match.group(0)

    # 使用正则表达式替换公式中的单元格引用
    adjusted_formula = re.sub(r"([A-Z]+)(\d+)(\D?)", replace_func, formula)
    return adjusted_formula